package backOffice;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;

public class JDBCCourseDAO {
	private Connexion connexion;
	private String sql; 
	public JDBCCourseDAO() {
		try {
			connexion=new Connexion();	
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("Connexion Fail");
		}
	}	 
	public Map<String,Course> getCourseList() {
		Map<String,Course> list = new HashMap<String,Course>();
		sql="select * from t_course";
		try {
			Statement stmt = connexion.con.createStatement();
			ResultSet rs = stmt.executeQuery(sql); 			
			while(rs.next()) {
				Course course = new Course(rs.getString(1), rs.getString(2));			
				list.put(rs.getString(1),course);	
			}
		} catch(SQLException e) {
			e.printStackTrace();
			System.out.println("find course Fail");
		}		
		return list;
	}
		
	public  Map<String,Location> getLocationList(){
		 Map<String,Location> list=new HashMap<String,Location>();
		 sql="select * from t_location";
			try {
				Statement stmt = connexion.con.createStatement();
				ResultSet rs = stmt.executeQuery(sql); 			
				while(rs.next()) {
					Location loc = new Location(Integer.valueOf(rs.getString(1)), rs.getString(2));			
					list.put(rs.getString(2),loc);	
				}
			} catch(SQLException e) {
				e.printStackTrace();
				System.out.println("find location Fail");
			}		
			return list;		 
	}

	public  Map<Integer,Session> getSessionList(){
		 Map<Integer,Session> list=new HashMap<Integer,Session>();
		 sql="select * from t_location";
			try {
				Statement stmt = connexion.con.createStatement();
				ResultSet rs = stmt.executeQuery(sql); 			
				while(rs.next()) {
					Session ses = new Session(Integer.valueOf(rs.getString(1)), rs.getString(2)
							, rs.getString(3), rs.getString(4), Integer.valueOf(rs.getString(5)));			
					list.put(Integer.valueOf(rs.getString(1)),ses);	
				}
			} catch(SQLException e) {
				e.printStackTrace();
				System.out.println("find location Fail");
			}		
			return list;		 
	}
	
	
	public Map<Integer, InfoCourse> getList(){
		Map<Integer,InfoCourse> list = new HashMap<Integer,InfoCourse>();
		 sql="select t.code, t.title, s.date_start, s.date_end, l.city, s.id from t_course t inner join t_session s on t.code=s.course_code inner join t_location l on l.id=s.location_id";
			try {
				Statement stmt = connexion.con.createStatement();
				ResultSet rs = stmt.executeQuery(sql); 	
				while(rs.next()) {
					InfoCourse ic = new InfoCourse(rs.getString(1), rs.getString(2)
							, rs.getString(3), rs.getString(4), rs.getString(5),rs.getInt(6));			
					list.put(rs.getInt(6),ic);	
				}
			} catch(SQLException e) {
				e.printStackTrace();
				System.out.println("getInfoCourse Fail");
			}		
			return list;			
	}
 	
	public int addCourse(String code, String title, String dbegin, String dend, String loc) throws InterruptedException {
		System.out.println("v3");
		Map<String, Course> listcourse=getCourseList();
		Map<String, Location> listlocation=getLocationList();		
		int rs=0 ;
		ResultSet rss;		
		try {
			Statement stmt = connexion.con.createStatement();
		
			if(!listcourse.containsKey(code)){				
				sql="insert into t_course values('"+code+"','"+title+"')";
				rs= stmt.executeUpdate(sql); 
			}			
			
			if(!dbegin.isEmpty()&&!dend.isEmpty()){
				if(!listlocation.containsKey(loc)){		
					sql="insert into t_location (city) values('"+loc+"')";
					rs= stmt.executeUpdate(sql); 
				}
				sql="select id from t_location where city='"+loc+"'";				
				rss= stmt.executeQuery(sql);			
				if(rss.next()){			
					sql="insert into t_session (date_start,date_end,course_code,location_id)  values('"+dbegin+"','"+dend+"','"+code+"','"+rss.getInt(1)+"')";
					rs= stmt.executeUpdate(sql); 
				}
			}						
			System.out.println("add course ok");
		} catch(SQLException e) {
			e.printStackTrace();
			System.out.println("add course Fail");
		}	
		return rs;
	} 
	
	
	
	public int deleteCourse(String sId) {	
		int rs=0 ;	
		try {
			Statement stmt = connexion.con.createStatement();			 	
			sql="delete from t_session where id='"+sId+"'";
			rs= stmt.executeUpdate(sql); 					
		} catch(SQLException e) {
			e.printStackTrace();
			System.out.println("delete course Fail");
		}	
		return rs;
	}
	
	public int updateCourse(String code, String title) {	
		Map<String, Course> listcourse=getCourseList();
		int rs=0 ;
		if(listcourse.containsKey(code)){
			System.out.println("update course Fail");
		}else{
			try {
				Statement stmt = connexion.con.createStatement();
				sql="update t_course set code='"+code+"', title='"+title+"' where code='"+code+"'";
				rs= stmt.executeUpdate(sql); 	
				sql="update t_session code='"+code+"' where code='"+code+"'";
				rs= stmt.executeUpdate(sql); 					
			} catch(SQLException e) {
				e.printStackTrace();
				System.out.println("delete course Fail");
			}	
		}
		return rs;
	}
 

}
